CREATE TABLE Club (
    ClubID INT PRIMARY KEY AUTO_INCREMENT,
    ClubName VARCHAR(100) NOT NULL UNIQUE,
    Category ENUM('Academic','Cultural','Sports','SpecialInterest') NOT NULL,
    FoundingDate DATE NOT NULL,
    Description TEXT,
    ClubStatus ENUM('Active','Inactive') NOT NULL DEFAULT 'Active'
);
describe Club;

CREATE TABLE Student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(120) UNIQUE NOT NULL,
  enrolment_year YEAR NOT NULL
);

CREATE TABLE Membership (
  membership_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  club_id INT NOT NULL,
  join_date DATE,
  FOREIGN KEY (student_id) REFERENCES Student(student_id)
      ON DELETE CASCADE,
  FOREIGN KEY (club_id) REFERENCES Club(club_id)
      ON DELETE CASCADE
);

Event Table
CREATE TABLE Event (
  event_id INT AUTO_INCREMENT PRIMARY KEY,
  club_id INT NOT NULL,
  event_name VARCHAR(120) NOT NULL,
  event_date DATE NOT NULL,
  venue VARCHAR(120),
  max_attendance INT CHECK (max_attendance > 0),
  FOREIGN KEY (club_id) REFERENCES Club(club_id)
);




CREATE TABLE Participation (
  participation_id INT AUTO_INCREMENT PRIMARY KEY,
  event_id INT NOT NULL,
  student_id INT NOT NULL,
  attendance_status VARCHAR(20),
  FOREIGN KEY (event_id) REFERENCES Event(event_id),
  FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

CREATE TABLE Announcement (
  announcement_id INT AUTO_INCREMENT PRIMARY KEY,
  club_id INT NOT NULL,
  title VARCHAR(150),
  message TEXT,
  send_date DATE,
  FOREIGN KEY (club_id) REFERENCES Club(club_id)
);

Insert Data:
INSERT INTO Club (club_name, category, founding_date) VALUES
('Computer Science Society', 'Academic', '2015-09-01'),
('Drama Club', 'Cultural', '2012-01-15'),
('Football Club', 'Sports', '2010-03-20');

select * from Club;
Students
INSERT INTO Student (first_name, last_name, email, enrolment_year) VALUES
('John', 'Mills', 'john.mills@uni.ac.uk', 2021),
('Maria', 'Singh', 'maria.singh@uni.ac.uk', 2022),
('Ahmed', 'Khan', 'ahmed.khan@uni.ac.uk', 2020);
select * from Student;
INSERT INTO Membership (student_id, club_id, join_date) VALUES
(1, 1, '2021-09-12'),
(1, 3, '2021-10-02'),
(2, 2, '2022-01-14');
select * from Membership;

INSERT INTO Event (club_id, event_name, event_date, venue, max_attendance) VALUES
(1, 'Hackathon 2023', '2023-11-10', 'IT Lab 3', 50),
(3, 'Football Trials', '2023-09-01', 'Main Pitch', 80);
select * from Event;

INSERT INTO Participation(event_id, student_id, attendance_status) VALUES
(1, 1, 'Attended'),
(1, 2, 'Not Attended'),
(2, 1, 'Attended');

select * from Participation;

INSERT INTO Announcement(club_id, title, message, send_date) VALUES
(1, 'Welcome to CS Society', 'First meeting next week!', '2023-09-15');
select * from Announcement;

Stored Procedure – INSERT Membership
DELIMITER $$

CREATE PROCEDURE add_membership(
 IN p_student INT,
 IN p_club INT,
 IN p_date DATE
)
BEGIN
  START TRANSACTION;

  INSERT INTO Membership(student_id, club_id, join_date)
  VALUES(p_student, p_club, p_date);

  IF ROW_COUNT() = 1 THEN
     COMMIT;
  ELSE
     ROLLBACK;
  END IF;
END $$

DELIMITER ;

Audit Table
CREATE TABLE Membership_Audit (
 audit_id INT AUTO_INCREMENT PRIMARY KEY,
 membership_id INT,
 action_type VARCHAR(20),
 action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
select * from Membership_Audit;

Trigger – Log Insert
CREATE TRIGGER trg_membership_insert
AFTER INSERT ON Membership
FOR EACH ROW
INSERT INTO Membership_Audit(membership_id, action_type)
VALUES (NEW.membership_id, 'INSERT');

START TRANSACTION;

INSERT INTO Event(club_id, event_name, event_date, venue, max_attendance)
VALUES (3, 'Team Briefing', '2023-12-01', 'Sports Hall', 40);

SET @eid = LAST_INSERT_ID();

INSERT INTO Participation(event_id, student_id, attendance_status)
VALUES (@eid, 1, 'Invited');

INSERT INTO Participation(event_id, student_id, attendance_status)
VALUES (@eid, 3, 'Invited');

DELIMITER $$

CREATE PROCEDURE check_participation(IN eid INT)
BEGIN
    START TRANSACTION;

    IF (SELECT COUNT(*) FROM Participation WHERE event_id = eid) = 2 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;

END $$

DELIMITER ;







